package com.baoyouqun.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baoyouqun.entity.City;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface CityMapper extends BaseMapper<City> {

    /**
     * 单个编码：查询自身及所有子级（省/市/区三层）
     */
    @Select("""
                SELECT code FROM city WHERE code = #{code}
                UNION ALL
                SELECT code FROM city WHERE parent_code = #{code}
                UNION ALL
                SELECT c3.code FROM city c1
                INNER JOIN city c2 ON c2.parent_code = c1.code
                INNER JOIN city c3 ON c3.parent_code = c2.code
                WHERE c1.code = #{code}
            """)
    List<String> getCodeAndAllChildren(@Param("code") String code);

    /**
     * 多个编码：查询所有编码及它们的子级（省/市/区三层）
     */
    @Select("""
                -- 自身
                SELECT code FROM city WHERE code IN
                <foreach collection="codes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                UNION ALL
                -- 直接子级
                SELECT code FROM city WHERE parent_code IN
                <foreach collection="codes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                UNION ALL
                -- 子级的子级
                SELECT c3.code FROM city c1
                INNER JOIN city c2 ON c2.parent_code = c1.code
                INNER JOIN city c3 ON c3.parent_code = c2.code
                WHERE c1.code IN
                <foreach collection="codes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
            """)
    List<String> getCodesAndAllChildren(@Param("codes") List<String> codes);

    /**
     * 查询所有省份（顶级地区：parentCode不在所有地区编码中）
     */
    @Select("""
                SELECT code, name 
                FROM city 
                WHERE parent_code is null 
                ORDER BY code
            """)
    List<City> selectAllProvinces();

    /**
     * 获取指定省份下所有县市编码（递归获取所有下级）
     *
     * @param provinceCode 省份编码
     * @return 该省下所有县市编码列表
     */
    @Select("""
                WITH RECURSIVE city_recursive AS (
                    -- 初始：查询省份直接下属的市
                    SELECT code FROM city WHERE parent_code = #{provinceCode}
                    UNION ALL
                    -- 递归：查询市下属的区县
                    SELECT c.code FROM city c
                    INNER JOIN city_recursive cr ON c.parent_code = cr.code
                )
                SELECT code FROM city_recursive
            """)
    List<String> selectAllDistrictCodesByProvince(@Param("provinceCode") String provinceCode);

    /**
     * 查询指定省下所有县市（仅直接下属的市）
     */
    @Select("SELECT code, name FROM city WHERE parent_code = #{provinceCode} ORDER BY code")
    List<City> selectCitiesByProvinceCode(@Param("provinceCode") String provinceCode);

    /**
     * 批量查询地区名称（通过编码列表）
     */
    @Select("""
                <script>
                SELECT code, name 
                FROM city 
                WHERE code IN 
                <foreach collection="areaCodes" item="code" open="(" separator="," close=")">
                    #{code}
                </foreach>
                </script>
            """)
    @MapKey("code")
    Map<String, City> batchSelectCityByCodes(@Param("areaCodes") List<String> areaCodes);
}